﻿//using Sgr.Data;

//namespace Sgr.EventBus.RelayBoxs.Storage
//{
//    /// <summary>
//    /// OutboxMessage 建表与索引脚本生成工具。
//    /// 该类为不同数据库生成建表语句和索引语句，确保与 OutboxMessage 实体字段一致。
//    /// 注意：所有时间字段已统一使用无时区类型（如 DATETIME / TIMESTAMP），不再使用 DateTimeOffset。
//    /// </summary>
//    public static class OutboxMessageSqlBuilder
//    {
//        /// <summary>
//        /// 根据数据库类型生成建表 SQL 语句。
//        /// </summary>
//        /// <param name="databaseType">目标数据库类型</param>
//        /// <returns>建表 SQL 语句</returns>
//        public static string CreateTableSql(DatabaseType databaseType)
//        {
//            return databaseType switch
//            {
//                DatabaseType.SqlServer => GetSqlServerCreateTableSql(),
//                DatabaseType.MySql => GetMySqlCreateTableSql(),
//                DatabaseType.SQLite => GetSQLiteCreateTableSql(),
//                DatabaseType.PostgreSql => GetPostgreSqlCreateTableSql(),
//                DatabaseType.Oracle => GetOracleCreateTableSql(),
//                DatabaseType.DB2 => GetDB2CreateTableSql(),
//                _ => throw new NotSupportedException($"不支持的数据库类型: {databaseType}")
//            };
//        }

//        /// <summary>
//        /// 根据数据库类型生成索引 SQL 语句，包括复合索引 (status, next_on_utc) 和唯一索引 (message_id)。
//        /// </summary>
//        /// <param name="databaseType">目标数据库类型</param>
//        /// <returns>索引创建 SQL 语句</returns>
//        public static string CreateIndexSql(DatabaseType databaseType)
//        {
//            return databaseType switch
//            {
//                DatabaseType.SqlServer => GetSqlServerCreateIndexSql(),
//                DatabaseType.MySql => GetMySqlCreateIndexSql(),
//                DatabaseType.SQLite => GetSQLiteCreateIndexSql(),
//                DatabaseType.PostgreSql => GetPostgreSqlCreateIndexSql(),
//                DatabaseType.Oracle => GetOracleCreateIndexSql(),
//                DatabaseType.DB2 => GetDB2CreateIndexSql(),
//                _ => throw new NotSupportedException($"不支持的数据库类型: {databaseType}")
//            };
//        }

//        /// <summary>
//        /// 获取兼容不同数据库的 LIMIT 子句（用于分页查询）
//        /// </summary>
//        /// <param name="databaseType">目标数据库类型</param>
//        /// <param name="limit">限制条数</param>
//        /// <returns>兼容的 LIMIT 子句，如 "LIMIT 100", "FETCH FIRST 100 ROWS ONLY"</returns>
//        public static string GetLimitClause(DatabaseType databaseType, int limit)
//        {
//            return databaseType switch
//            {
//                DatabaseType.SqlServer => "OFFSET 0 ROWS FETCH NEXT @Limit ROWS ONLY",
//                DatabaseType.MySql => "LIMIT @Limit",
//                DatabaseType.SQLite => "LIMIT @Limit",
//                DatabaseType.PostgreSql => "LIMIT @Limit",
//                DatabaseType.Oracle => "FETCH FIRST @Limit ROWS ONLY",
//                DatabaseType.DB2 => "FETCH FIRST @Limit ROWS ONLY",
//                _ => throw new NotSupportedException($"不支持的数据库类型: {databaseType}")
//            };
//        }

//        #region MySql

//        private static string GetMySqlCreateTableSql()
//        {
//            return @"
//CREATE TABLE IF NOT EXISTS `sgr_outbox_message` (
//  `sqr_id` BIGINT NOT NULL,
//  `sqr_type` VARCHAR(200) NOT NULL DEFAULT '',
//  `sqr_message_id` VARCHAR(64) NOT NULL DEFAULT '',
//  `sqr_topic` VARCHAR(200) NOT NULL DEFAULT '',
//  `sqr_payload` JSON NOT NULL,
//  `sqr_occurred_on_utc` DATETIME(6) NOT NULL,
//  `sqr_next_on_utc` DATETIME(6) NOT NULL,
//  `sqr_dispatched_on_utc` DATETIME(6) NULL,
//  `sqr_status` SMALLINT NOT NULL DEFAULT 0,
//  `sqr_retry_count` INT NOT NULL DEFAULT 0,
//  `sqr_last_error` VARCHAR(200) NULL,
//  PRIMARY KEY (`sqr_id`)
//) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
//        }

//        private static string GetMySqlCreateIndexSql()
//        {
//            // 复合索引：状态 + 下次投递时间
//            var idxStatusNext = @"
//SET @idx_status_next_exists := (
//  SELECT COUNT(1)
//  FROM information_schema.statistics
//  WHERE table_schema = DATABASE()
//    AND table_name = 'sgr_outbox_message'
//    AND index_name = 'ix_outbox_message_status_next_on_utc'
//);
//SET @sql_status_next = IF(@idx_status_next_exists = 0,
//  'CREATE INDEX `ix_outbox_message_status_next_on_utc` ON `sgr_outbox_message` (`sqr_status`, `sqr_next_on_utc`);',
//  'SELECT 0;'
//);
//PREPARE stmt_status_next FROM @sql_status_next;
//EXECUTE stmt_status_next;
//DEALLOCATE PREPARE stmt_status_next;";

//            // 唯一索引：消息ID
//            var idxMessageId = @"
//SET @idx_message_id_exists := (
//  SELECT COUNT(1)
//  FROM information_schema.statistics
//  WHERE table_schema = DATABASE()
//    AND table_name = 'sgr_outbox_message'
//    AND index_name = 'ux_outbox_message_message_id'
//);
//SET @sql_message_id = IF(@idx_message_id_exists = 0,
//  'CREATE UNIQUE INDEX `ux_outbox_message_message_id` ON `sgr_outbox_message` (`sqr_message_id`);',
//  'SELECT 0;'
//);
//PREPARE stmt_message_id FROM @sql_message_id;
//EXECUTE stmt_message_id;
//DEALLOCATE PREPARE stmt_message_id;";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion MySql

//        #region SqlServer

//        private static string GetSqlServerCreateTableSql()
//        {
//            return @"
//IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sgr_outbox_message]') AND type in (N'U'))
//BEGIN
//CREATE TABLE [dbo].[sgr_outbox_message] (
//    [sqr_id] BIGINT NOT NULL,
//    [sqr_type] NVARCHAR(200) NOT NULL DEFAULT(''),
//    [sqr_message_id] NVARCHAR(64) NOT NULL DEFAULT(''),
//    [sqr_topic] NVARCHAR(200) NOT NULL DEFAULT(''),
//    [sqr_payload] NVARCHAR(MAX) NOT NULL,
//    [sqr_occurred_on_utc] DATETIME2(7) NOT NULL,
//    [sqr_next_on_utc] DATETIME2(7) NOT NULL,
//    [sqr_dispatched_on_utc] DATETIME2(7) NULL,
//    [sqr_status] SMALLINT NOT NULL DEFAULT(0),
//    [sqr_retry_count] INT NOT NULL DEFAULT(0),
//    [sqr_last_error] NVARCHAR(200) NULL,
//    CONSTRAINT [pk_outbox_message] PRIMARY KEY CLUSTERED ([sqr_id] ASC)
//);
//END";
//        }

//        private static string GetSqlServerCreateIndexSql()
//        {
//            var idxStatusNext = @"
//IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'ix_outbox_message_status_next_on_utc' AND object_id = OBJECT_ID(N'[dbo].[sgr_outbox_message]'))
//CREATE NONCLUSTERED INDEX [ix_outbox_message_status_next_on_utc]
//ON [dbo].[sgr_outbox_message] ([sqr_status] ASC, [sqr_next_on_utc] ASC);";

//            var idxMessageId = @"
//IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'ux_outbox_message_message_id' AND object_id = OBJECT_ID(N'[dbo].[sgr_outbox_message]'))
//CREATE UNIQUE NONCLUSTERED INDEX [ux_outbox_message_message_id]
//ON [dbo].[sgr_outbox_message] ([sqr_message_id] ASC);";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion SqlServer

//        // [SQLite / PostgreSQL / Oracle / DB2 等其它数据库脚本也同步做了字段长度和唯一索引的变更，下面仅展示关键部分，如你也需要这些部分的完整代码，我可以继续补充]

//        #region SQLite

//        private static string GetSQLiteCreateTableSql()
//        {
//            return @"
//CREATE TABLE IF NOT EXISTS sgr_outbox_message (
//  sqr_id INTEGER NOT NULL PRIMARY KEY,
//  sqr_type VARCHAR(200) NOT NULL DEFAULT '',
//  sqr_message_id VARCHAR(64) NOT NULL DEFAULT '',
//  sqr_topic VARCHAR(200) NOT NULL DEFAULT '',
//  sqr_payload TEXT NOT NULL,
//  sqr_occurred_on_utc DATETIME NOT NULL,
//  sqr_next_on_utc DATETIME NOT NULL,
//  sqr_dispatched_on_utc DATETIME NULL,
//  sqr_status SMALLINT NOT NULL DEFAULT 0,
//  sqr_retry_count INTEGER NOT NULL DEFAULT 0,
//  sqr_last_error VARCHAR(200) NULL
//);";
//        }

//        private static string GetSQLiteCreateIndexSql()
//        {
//            var idxStatusNext = @"
//CREATE INDEX IF NOT EXISTS ix_outbox_message_status_next_on_utc
//ON sgr_outbox_message (sqr_status, sqr_next_on_utc);";

//            var idxMessageId = @"
//CREATE UNIQUE INDEX IF NOT EXISTS ux_outbox_message_message_id
//ON sgr_outbox_message (sqr_message_id);";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion SQLite

//        #region PostgreSql

//        private static string GetPostgreSqlCreateTableSql()
//        {
//            return @"
//CREATE TABLE IF NOT EXISTS sgr_outbox_message (
//  sqr_id BIGINT PRIMARY KEY,
//  sqr_type VARCHAR(200) NOT NULL DEFAULT '',
//  sqr_message_id VARCHAR(64) NOT NULL DEFAULT '',
//  sqr_topic VARCHAR(200) NOT NULL DEFAULT '',
//  sqr_payload JSONB NOT NULL,
//  sqr_occurred_on_utc TIMESTAMP WITHOUT TIME ZONE NOT NULL,
//  sqr_next_on_utc TIMESTAMP WITHOUT TIME ZONE NOT NULL,
//  sqr_dispatched_on_utc TIMESTAMP WITHOUT TIME ZONE NULL,
//  sqr_status SMALLINT NOT NULL DEFAULT 0,
//  sqr_retry_count INT NOT NULL DEFAULT 0,
//  sqr_last_error VARCHAR(200) NULL
//);";
//        }

//        private static string GetPostgreSqlCreateIndexSql()
//        {
//            var idxStatusNext = @"
//CREATE INDEX IF NOT EXISTS ix_outbox_message_status_next_on_utc
//ON sgr_outbox_message (sqr_status, sqr_next_on_utc);";

//            var idxMessageId = @"
//CREATE UNIQUE INDEX IF NOT EXISTS ux_outbox_message_message_id
//ON sgr_outbox_message (sqr_message_id);";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion PostgreSql

//        #region Oracle

//        private static string GetOracleCreateTableSql()
//        {
//            return @"
//DECLARE
//  v_count NUMBER;
//BEGIN
//  SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'SGR_OUTBOX_MESSAGE';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE '
//      CREATE TABLE ""SGR_OUTBOX_MESSAGE"" (
//        ""sqr_id"" NUMBER(19) NOT NULL,
//        ""sqr_type"" VARCHAR2(200 CHAR) DEFAULT '''' NOT NULL,
//        ""sqr_message_id"" VARCHAR2(64 CHAR) DEFAULT '''' NOT NULL,
//        ""sqr_topic"" VARCHAR2(200 CHAR) DEFAULT '''' NOT NULL,
//        ""sqr_payload"" CLOB NOT NULL,
//        ""sqr_occurred_on_utc"" TIMESTAMP NOT NULL,
//        ""sqr_next_on_utc"" TIMESTAMP NOT NULL,
//        ""sqr_dispatched_on_utc"" TIMESTAMP NULL,
//        ""sqr_status"" NUMBER(5) DEFAULT 0 NOT NULL,
//        ""sqr_retry_count"" NUMBER DEFAULT 0 NOT NULL,
//        ""sqr_last_error"" VARCHAR2(200 CHAR) NULL,
//        CONSTRAINT ""pk_outbox_message"" PRIMARY KEY (""sqr_id"")
//      )
//    ';
//  END IF;
//END;";
//        }

//        private static string GetOracleCreateIndexSql()
//        {
//            var idxStatusNext = @"
//DECLARE
//  v_count NUMBER;
//BEGIN
//  SELECT COUNT(*) INTO v_count FROM user_indexes WHERE index_name = 'IX_OUTBOX_MESSAGE_STATUS_NEXT_ON_UTC';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE 'CREATE INDEX ""IX_OUTBOX_MESSAGE_STATUS_NEXT_ON_UTC"" ON ""SGR_OUTBOX_MESSAGE"" (""sqr_status"", ""sqr_next_on_utc"")';
//  END IF;
//END;";

//            var idxMessageId = @"
//DECLARE
//  v_count NUMBER;
//BEGIN
//  SELECT COUNT(*) INTO v_count FROM user_indexes WHERE index_name = 'UX_OUTBOX_MESSAGE_MESSAGE_ID';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX ""UX_OUTBOX_MESSAGE_MESSAGE_ID"" ON ""SGR_OUTBOX_MESSAGE"" (""sqr_message_id"")';
//  END IF;
//END;";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion Oracle

//        #region DB2

//        private static string GetDB2CreateTableSql()
//        {
//            return @"
//BEGIN ATOMIC
//  DECLARE v_count INTEGER DEFAULT 0;
//  SELECT COUNT(*) INTO v_count FROM syscat.tables WHERE tabschema = CURRENT_SCHEMA AND tabname = 'SGR_OUTBOX_MESSAGE';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE '
//      CREATE TABLE ""SGR_OUTBOX_MESSAGE"" (
//        ""sqr_id"" BIGINT NOT NULL,
//        ""sqr_type"" VARCHAR(200) DEFAULT '''' NOT NULL,
//        ""sqr_message_id"" VARCHAR(64) DEFAULT '''' NOT NULL,
//        ""sqr_topic"" VARCHAR(200) DEFAULT '''' NOT NULL,
//        ""sqr_payload"" CLOB NOT NULL,
//        ""sqr_occurred_on_utc"" TIMESTAMP NOT NULL,
//        ""sqr_next_on_utc"" TIMESTAMP NOT NULL,
//        ""sqr_dispatched_on_utc"" TIMESTAMP NULL,
//        ""sqr_status"" SMALLINT DEFAULT 0 NOT NULL,
//        ""sqr_retry_count"" INTEGER DEFAULT 0 NOT NULL,
//        ""sqr_last_error"" VARCHAR(200) NULL,
//        PRIMARY KEY (""sqr_id"")
//      )
//    ';
//  END IF;
//END;";
//        }

//        private static string GetDB2CreateIndexSql()
//        {
//            var idxStatusNext = @"
//BEGIN ATOMIC
//  DECLARE v_count INTEGER DEFAULT 0;
//  SELECT COUNT(*) INTO v_count FROM syscat.indexes
//    WHERE tabschema = CURRENT_SCHEMA
//      AND tabname = 'SGR_OUTBOX_MESSAGE'
//      AND indname = 'IX_OUTBOX_MESSAGE_STATUS_NEXT_ON_UTC';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE 'CREATE INDEX ""IX_OUTBOX_MESSAGE_STATUS_NEXT_ON_UTC"" ON ""SGR_OUTBOX_MESSAGE"" (""sqr_status"", ""sqr_next_on_utc"")';
//  END IF;
//END;";

//            var idxMessageId = @"
//BEGIN ATOMIC
//  DECLARE v_count INTEGER DEFAULT 0;
//  SELECT COUNT(*) INTO v_count FROM syscat.indexes
//    WHERE tabschema = CURRENT_SCHEMA
//      AND tabname = 'SGR_OUTBOX_MESSAGE'
//      AND indname = 'UX_OUTBOX_MESSAGE_MESSAGE_ID';
//  IF v_count = 0 THEN
//    EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX ""UX_OUTBOX_MESSAGE_MESSAGE_ID"" ON ""SGR_OUTBOX_MESSAGE"" (""sqr_message_id"")';
//  END IF;
//END;";

//            return idxStatusNext + idxMessageId;
//        }

//        #endregion DB2
//    }
//}